package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.*;

public class DBCustomer implements IFDBGeneric<Customer> {

	private Connection con;

	public DBCustomer() {
		con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Customer> getAll() {
		return miscWhere("");
	}
	

	public ArrayList<Customer> getAll_byType(String type){
		return miscWhere("type='"+type+"'");
	}

	public Customer find(String name) {
		return singleWhere("name = '" + name + "'");
	}

	public Customer find_byID(int id) {
		return singleWhere("sid = " + id);
	}

	public int delete(int sid) {
		int rc = -1;

		String query = "DELETE FROM People WHERE pid = " + sid;

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.err.println("Couldn't delete the Customer with id " + sid);
		}

		return rc;
	}

	public int insert(Customer costumer) throws Exception {
		int rc = -1;

		String query1 = "INSERT INTO People (name,address,phoneNo,email) VALUES "
				+ "('"
				+ costumer.getName()
				+ "','"
				+ costumer.getAddress()
				+ "','"
				+ costumer.getPhoneNo()
				+ "','"
				+ costumer.getEmail()
				+ "')";

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);

			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next())
				rc = rs.getInt(1);
			rs.close();

			if (rc > 0) {

				String query2 = "INSERT INTO Customer (Cid,type) VALUES " + "("
						+ rc + ",'" + costumer.getType() + "')";

				rc = stmt.executeUpdate(query2);
			}

			stmt.close();
		} catch (Exception ex) {
			System.err.println("Couldn't insert the Customer with name "
					+ costumer.getName());
		}

		return rc;
	}

	public int update(Customer costumer) {
		int rc = -1;

		String query1 = "UPDATE People SET name = '" + costumer.getName()
				+ "',address = '" + costumer.getAddress() + "',"
				+ "phoneNo = '" + costumer.getPhoneNo() + "',email = '"
				+ costumer.getEmail() + "' WHERE pid = " + costumer.getPid();

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);

			if (rc > 0) {
				String query2 = "UPDATE Customer SET type = '"
						+ costumer.getType() + "' WHERE sid = "
						+ costumer.getPid();
				rc = stmt.executeUpdate(query2);
			}

			stmt.close();
		} catch (Exception ex) {
			System.err.println("Couldn't update the Customer with name "
					+ costumer.getName());
		}

		return rc;
	}

	private Customer singleWhere(String wClause) {
		Customer Customer = null;

		ResultSet results;
		String query = buildQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next())
				Customer = buildCustomer(results);

			stmt.close();
		} catch (Exception ex) {
		}

		return Customer;
	}

	private ArrayList<Customer> miscWhere(String wClause) {
		ArrayList<Customer> Customers = new ArrayList<>();

		ResultSet results;
		String query = buildQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Customer Customer = buildCustomer(results);
				Customers.add(Customer);
			}

			stmt.close();

		} catch (Exception ex) {
			System.err.println("Couldn't select multiple customer tuples !");
		}

		return Customers;
	}

	private Customer buildCustomer(ResultSet result) {
		Customer Customer = new Customer();

		try {
			Customer.setPid(result.getInt("cid"));
			Customer.setType(result.getString("type"));
			Customer.setName(result.getString("name"));
			Customer.setAddress(result.getString("address"));
			Customer.setPhoneNo(result.getString("phoneNo"));
			Customer.setEmail(result.getString("email"));
		} catch (Exception ex) {
			System.err.println("Couldn't build the customer !");
		}

		return Customer;
	}

	private String buildQuery(String wClause) {
		String query = "Select sid,companyName,cvr,name,address,phoneNo,email from Customer,People where sid=pid";

		if (wClause.length() > 0)
			query = query + " AND " + wClause;

		return query;
	}

}
